<?php
    require_once("spotify.php");
    require_once("tinyurl.php");

    /* Slēdzamies pie DB */

    mysql_connect(MYSQL_HOST, MYSQL_USER, MYSQL_PASSWORD);
    mysql_select_db(MYSQL_DB);   

	# paarbaudam jaunumus
	check_updates();
	check_daily_updates();

	function check_updates() {
		# paskataamies timestamp.txt - vai jaataisa update
		$stamp = file_get_contents('timestamp.txt','r');
		if (time()-$stamp > 60) {
			# jaa, jaatjauninaas
			$f = fopen('timestamp.txt','w');
			fputs($f, time());
			fclose($f);
			
			update_db();
		}
	}

	function check_daily_updates() {
		# paskataamies timestamp2.txt - vai jaataisa update
		$stamp = file_get_contents('timestamp2.txt','r');
		if (time() - $stamp > 60 * 60 * 12) {
			# jaa, jaatjauninaas
			$f = fopen('timestamp2.txt','w');
			fputs($f, time());
			fclose($f);
			
			require_once('update_klab.php');
		}
	}

	
	function update_db() {
		$data = file_get_contents('http://www.radioswh.lv/bilboard/swh_live_new/songhist.php?lang=lv','r');				
		#$data = file_get_contents('data.txt','r');				
		
		preg_match_all("/<TD BGCOLOR='#d4d4d4' WIDTH=400>&nbsp;&nbsp;<FONT FACE='Arial' SIZE=2>(.*) - (.*)<\/FONT><\/TD>.*<TD BGCOLOR='#d4d4d4' WIDTH=50>&nbsp;&nbsp;<FONT FACE='Arial' SIZE=2>(.*):(.*)&nbsp;(.*)&nbsp;<\/FONT><\/TD>/sU",$data,$matches);
		$titles = $matches[1];
		$artists = $matches[2];
		$hours = $matches[3];
		$mins = $matches[4];
		$secs = $matches[5];
		$now = time();
		$max_timestamp = max_timestamp();
		
		for ($i = 0; $i < count($titles); $i++) {
			$timestamp = mktime($hours[$i],$mins[$i],$secs[$i]);
			# midnight?
      # 
      # mktime above doesn't get date, so it assumes today
      # problem is if we at 00:03 register a song played in 23:57...
      # we add 3h to $now so daylight saving doesn't ruin things. We can do 
      # this because we know: if $timestamp _is_ bigger than $now, 
      # then the difference will be approx 24h +/- 2h 
			if ($timestamp > $now + 60 * 60 * 3) $timestamp -= 86400;
			
			# ir jauns?
			if ($max_timestamp < $timestamp) {
				$artists[$i] = htmlspecialchars($artists[$i], ENT_QUOTES);
				$titles[$i] = htmlspecialchars($titles[$i], ENT_QUOTES);
				
				$artist_id = insert_artist($artists[$i]);
				$song_id = insert_song($artist_id, $titles[$i]);
				if ($i < 10) {
				    # No more than 10 requests per go!
				    insert_spotify_id($song_id, $artists[$i], $titles[$i]);
				}
				 
				$hour = strftime('%H', $timestamp); 
				$date = strftime('%d', $timestamp); 
				$month = strftime('%m', $timestamp); 				
				
				insert_event($artist_id, $song_id, $timestamp, $hour, $date, $month);
			} else break; # jaunumi beigushies
		}
	}

  function fetchall($sql) {
    $rez = mysql_query($sql);
    $resultset = array();
    while ($row = mysql_fetch_assoc($rez)) $resultset[] = $row;
    return $resultset;        
  }  
  
  function fetchone($sql) {
		return mysql_fetch_assoc(mysql_query($sql));
  }


	function max_timestamp() {
		$sql = 'select max(timestamp) as max from atsk';
		$res = fetchone($sql);
		return $res['max'];
	}
	
	function insert_artist($name) {
		$sql = "select id from izp where name = '$name'";
		$res = fetchone($sql);
		if (!$res) {
			# jaaveic inserts 
			$sql = " insert into izp (name) values ('$name') ";
			mysql_query($sql);
 
			# jaauzzin pieshkirtais id
			$sql = "select id from izp where name = '$name'";
			$res = fetchone($sql);			
		}
		return $res['id'];
	}

	function insert_song($artist_id, $name) {
		$sql = "select id from dz where name = '$name' and izp_id = $artist_id";
		$res = fetchone($sql);
		if (!$res) {
			# jaaveic inserts 
			$sql = " insert into dz (izp_id, name) values ($artist_id,'$name') ";
			mysql_query($sql);
 
			# jaauzzin pieshkirtais id
			$sql = "select id from dz where name = '$name' and izp_id = $artist_id";
			$res = fetchone($sql);			
		}
		
		$song_id = $res['id'];
		# jaapalielina atsk_cnt
		$sql = "update dz set atsk_cnt = atsk_cnt + 1 where id = $song_id";
		mysql_query($sql);
		return $song_id;
	}
	 
	function insert_event($artist_id, $song_id, $timestamp, $hour, $date, $month) {
		$sql = "insert into atsk (izp_id, dz_id, timestamp, hour, date, month)
						          values ($artist_id, $song_id, $timestamp, $hour, $date, $month) ";
		mysql_query($sql);
	}
	
	function insert_spotify_id($song_id, $artist_name, $song_name) {
		$sql = "select spotify_id from dz where id = $song_id";
		$res = fetchone($sql);
		if ($res && !$res['spotify_id']) {
			$spotify_id = get_spotify_id($artist_name, $song_name);
            if ($spotify_id) {
			    $sql = " update dz set spotify_id = '$spotify_id' where id = $song_id ";
			    mysql_query($sql);
			}
		}
	}

	function get_last20() {
		$sql = " select a.timestamp as timestamp, a.izp_id as izp_id,
							i.name as izp_name, dz.name as dz_name,
							dz.spotify_id as spotify_id
						from atsk a
						left join izp i on i.id = a.izp_id
						left join dz on dz.id = a.dz_id
						order by timestamp desc
						limit 20 ";
		return fetchall($sql);
	}

	function get_artist_list($sort) {
		if (!$sort) $sort = 'artist';
		$sql = " select i.id as id, i.name as name, 
							count(a.timestamp) as total, max(a.timestamp) as last
							from izp i
							left join atsk a on a.izp_id = i.id
							group by i.id, i.name ";
	  if ($sort == 'artist') $sql .= " order by name ";
	  if ($sort == 'atsk_cnt') $sql .= " order by total desc";
	  if ($sort == 'last_atsk') $sql .= " order by last desc ";
		
		$res = fetchall($sql);
		$now = time();
		$midnight = mktime(0,0,0, strftime('%m'),strftime('%d'),strftime('%Y'));
		$from_midnight = $now - $midnight;
		
		for ($i = 0; $i < count($res); $i++) {
			$daycnt = ($now - $res[$i]['last']);
			# ja jau paspeejusi buut pusnakts, pieskaitaam vienu dienu
			if ($daycnt > $from_midnight) $daycnt += 86400;
			$daycnt = intval($daycnt / 86400);
			
			$res[$i]['daycnt'] = $daycnt;
		}
		return $res;
	}
	
	function day_literal($n) {
		if ($n == 0) return 'šodien';
		if ($n == 1) return 'vakardien';
		return 'pirms %s dienām';
	}

	function get_artist_songs($id, $sort) {
		if (!$sort) $sort = 'song';
		$sql = "
			select dz.id as id, dz.name as dz_name, 
			count(a.timestamp) as total, max(a.timestamp) as last,
			dz.spotify_id as spotify_id
			from dz
			left join atsk a on a.dz_id = dz.id
			where dz.izp_id = $id
			group by dz.id, dz.name	";
			
	  if ($sort == 'song') $sql .= " order by name ";
	  if ($sort == 'atsk_cnt') $sql .= " order by total desc";
	  if ($sort == 'last_atsk') $sql .= " order by last desc ";
		
		$res = fetchall($sql);
		$now = time();
		$midnight = mktime(0,0,0, strftime('%m'),strftime('%d'),strftime('%Y'));
		$from_midnight = $now - $midnight;
		
		for ($i = 0; $i < count($res); $i++) {
			$daycnt = ($now - $res[$i]['last']);
			# ja jau paspeejusi buut pusnakts, pieskaitaam vienu dienu
			if ($daycnt > $from_midnight) $daycnt += 86400;
			$daycnt = intval($daycnt / 86400);
			
			$res[$i]['daycnt'] = $daycnt;
		}
		return $res;
	
	}
	
	function get_artist_name($id) {
		$sql = "select name from izp where id = $id ";
		$res = fetchone($sql);
		return $res['name'];
	}
	
	function get_new20() {
		$sql = " select a.izp_id as izp_id,
							i.name as izp_name, dz.name as dz_name,
							dz.spotify_id as spotify_id
							from atsk a
							left join izp i on i.id = a.izp_id
							left join dz on dz.id = a.dz_id
							where dz.atsk_cnt = 1
							order by timestamp desc
							limit 20 ";
		return fetchall($sql);
	}
	
	function get_top40($type) {
		if ($type == 'all') {
		$sql = " select i.name as izp, dz.name as dz_name, dz.atsk_cnt as atsk_cnt,
							max(a.timestamp) as last, i.id as izp_id,
							dz.spotify_id as spotify_id
							from dz
							left join izp i on dz.izp_id = i.id
							left join atsk a on a.dz_id = dz.id
							group by i.name, dz.name, dz.atsk_cnt
							order by atsk_cnt desc
							limit 40 ";
		}
		
		if ($type == 'day') {
		$stamp_limit = time() - 60*60*24;
		$sql = " select
							i.name as izp, i.id as izp_id, dz.name as dz_name,
							count(a.timestamp) as atsk_cnt, max(a.timestamp) as last,
							dz.spotify_id as spotify_id
							from atsk a
							left join dz on a.dz_id = dz.id
							left join izp i on a.izp_id = i.id
							where a.timestamp > $stamp_limit
							group by i.name, i.id, dz.name
							order by atsk_cnt desc
							limit 10;";
		}
		
		if ($type == 'week') {
		$stamp_limit = time() - 60*60*24*7;
		$sql = " select
							i.name as izp, i.id as izp_id, dz.name as dz_name,
							count(a.timestamp) as atsk_cnt, max(a.timestamp) as last,
							dz.spotify_id as spotify_id
							from atsk a
							left join dz on a.dz_id = dz.id
							left join izp i on a.izp_id = i.id
							where a.timestamp > $stamp_limit
							group by i.name, i.id, dz.name
							order by atsk_cnt desc
							limit 20;";
		}

		if ($type == 'month') {
		$stamp_limit = time() - 60*60*24*30;
		$sql = " select
							i.name as izp, i.id as izp_id, dz.name as dz_name,
							count(a.timestamp) as atsk_cnt, max(a.timestamp) as last,
							dz.spotify_id as spotify_id
							from atsk a
							left join dz on a.dz_id = dz.id
							left join izp i on a.izp_id = i.id
							where a.timestamp > $stamp_limit
							group by i.name, i.id, dz.name
							order by atsk_cnt desc
							limit 20;";
		}

		if ($type == 'year') {
		$stamp_limit = time() - 60*60*24*7*30*365;
		$sql = " select
							i.name as izp, i.id as izp_id, dz.name as dz_name,
							count(a.timestamp) as atsk_cnt, max(a.timestamp) as last,
							dz.spotify_id as spotify_id
							from atsk a
							left join dz on a.dz_id = dz.id
							left join izp i on a.izp_id = i.id
							where a.timestamp > $stamp_limit
							group by i.name, i.id, dz.name
							order by atsk_cnt desc
							limit 40;";
		}


		$res = fetchall($sql);
		$now = time();
		$midnight = mktime(0,0,0, strftime('%m'),strftime('%d'),strftime('%Y'));
		$from_midnight = $now - $midnight;
		
		for ($i = 0; $i < count($res); $i++) {
			$daycnt = ($now - $res[$i]['last']);
			# ja jau paspeejusi buut pusnakts, pieskaitaam vienu dienu
			if ($daycnt > $from_midnight) $daycnt += 86400;
			$daycnt = intval($daycnt / 86400);
			
			$res[$i]['daycnt'] = $daycnt;
		}
		return $res;
	}
	
?>
